﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Pr0ject0.ViewModel;

namespace Pr0ject0.Models
{
    public class JobModelDB
    {
        private string connection;
        private List<JobModel> jobs;
        private List<JobViewModel> Vjobs;
        private StoreDB storeDB;

        public List<JobModel> JobsList 
        {
            get { return new List<JobModel>(jobs); } 
        }
        public List<JobViewModel> VJobsList
        {
            get { return new List<JobViewModel>(Vjobs); }
        }

        private volatile static JobModelDB singleton;
        private static readonly object lockObj = new object();

        public static JobModelDB GetInstance()
        {
            if (singleton == null)
            {
                lock (lockObj)
                {
                    if (singleton == null) singleton = new JobModelDB();
                }
            }

            return singleton;
        }

        public static JobModelDB GetInstance(string connectionString)
        {
            
            if (singleton == null)
            {
                lock (lockObj)
                {
                    if (singleton == null) singleton = new JobModelDB(connectionString);
                }
            }

            return singleton;
        }

        private JobModelDB()
        {
            connection = "DB";
            storeDB = new StoreDB();
            update();
        }

        private JobModelDB(string connectionString)
        {
            connection = connectionString;
            storeDB = new StoreDB(connection);
            update();
        }

        public void addJobs(JobModel job)
        {
            int max;
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand storeIDCmd = new SqlCommand("SELECT Max(JobID) FROM Job", conn);
            SqlCommand insertCmd;
            
            conn.Open();
            SqlDataReader reader = storeIDCmd.ExecuteReader();
            reader.Read();
            max = reader.GetInt32(0);
            reader.Close();
            insertCmd = new SqlCommand(string.Format(
                "INSERT INTO Job(JobID, Title,DepartmentID , StoreID, Salary, isClosed) VALUES ( {0}, {1}, {2}, {3}, {4}, 0)",
                 max+1, job.title, job.departmentId, job.storeId, job.salary), conn);

            insertCmd.ExecuteNonQuery();
            conn.Close();

            update();
        }

        public void update()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand cmd = new SqlCommand("SELECT * FROM [JobModel]", conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            Vjobs = new List<JobViewModel>();

            while (reader.Read())
            {
                JobViewModel model = new JobViewModel();
                model.ID= Convert.ToInt32(reader["JobID"].ToString());
                model.title = reader["Title"].ToString();
                model.department = reader["Department"].ToString();
                model.departmentId = Convert.ToInt32(reader["DepartmentID"].ToString());
                model.storeId = Convert.ToInt32(reader["StoreID"].ToString());
                model.store = reader["Store"].ToString();
                model.salary = Convert.ToSingle(reader["Salary"].ToString());
                Vjobs.Add(model);
            }

            reader.Close();
            conn.Close();
        }

        public List<KeyValuePair<string ,List<JobViewModel>>> GetJobsList(
            string store, int title, string department, int distance, float salary)
        {
            List<JobViewModel> list;
            List<StoreModel> stores = new List<StoreModel>();
            StoreModel _store = storeDB.StoreList.Find(x => x.name == store);

            List<KeyValuePair<string, List<JobViewModel>>> _return = new List<KeyValuePair<string, List<JobViewModel>>>();

            foreach (StoreModel place in storeDB.StoreList)
            {
                double dist = this.distance(_store.longitude, _store.latitude, place.longitude, place.latitude);
                if (dist < distance)
                {
                    stores.Add(place);
                }
            }

            list = new List<JobViewModel>(from model in Vjobs
                                          where stores.Find(x => model.store == x.name) != null
                                          && model.salary >= salary
                                          select model);
            if (department != null)
            {
                list.RemoveAll(x => x.department != department);
            }

            try
            {
                string _title = JobTitleDB.GetInstance().jobTitles[title];
                list.RemoveAll(x => x.title != _title);
            }catch(KeyNotFoundException e){
            }

            foreach (StoreModel location in stores)
            {
                _return.Add(new KeyValuePair<string, List<JobViewModel>>(location.name,
                    list.FindAll(x => x.store == location.name)));
            }

            return _return;
        }

        public List<string> GetDepartments()
        {
            List<string> departements;
            IEnumerable<string> names = from model in Vjobs
                                        select model.department;
            departements = new List<string>(names.Distinct());
            return departements;
        }

        private double distance(double long1, double lat1, double long2, double lat2)
        {
            double radius = 3959;
            double dLat = Math.PI * (lat2 - lat1) / 180.0d;
            double dLong = Math.PI * (long2 - long1) / 180.0d;
            double _lat1 = Math.PI * lat1 / 180.0d;
            double _lat2 = Math.PI * lat2 / 180.0d;

            double a = (Math.Sin(dLat / 2.0d) * Math.Sin(dLat / 2.0d)) + 
                ((Math.Sin(dLong / 2.0d) * Math.Sin(dLong / 2.0d)) * 
                Math.Cos(_lat1) * Math.Cos(_lat2));
            double c = 2.0d * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1.0d - a));
            double d = radius * c;
            
            return d;
        }

        public void CloseJobs(int[] ids)
        {
            string idList = " JobID=" + ids[0];
            string sql = "Update Job Set isClosed = 1 Where";

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());

            for (int i = 1; i < ids.Length; i++)
            {
                idList += " OR JobID=" + ids[i];
            }
            sql += idList;
            SqlCommand cmd = new SqlCommand(sql, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            update();

        }
    }
}